﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Shapes;
using static Microsoft.EntityFrameworkCore.DbLoggerCategory;

namespace homework10
{
    /// <summary>
    /// WinQuery.xaml 的交互逻辑
    /// </summary>
    public partial class WinQuery : Window
    {
        public WinQuery()
        {
            InitializeComponent();
        }

        private int queryType = 1;

        private void btnClose_Click(object sender, RoutedEventArgs e)
        {
            this.Close();
        }

        private void Window_Loaded(object sender, RoutedEventArgs e)
        {
            cbxGender.Items.Clear();
            cbxGender.Items.Add("男");
            cbxGender.Items.Add("女");
            cbxGender.SelectedIndex = -1;
            ckDate.IsChecked = false;
            ckGender.IsChecked = false;

            dtpFrom.SelectedDateFormat= DatePickerFormat.Short;
            dtpFrom.SelectedDate = System.DateTime.Now;
            dtpTo.SelectedDateFormat = DatePickerFormat.Short;
            dtpTo.SelectedDate = System.DateTime.Now;

            cbxGender.IsEnabled = false;
            dtpFrom.IsEnabled = false;
            dtpTo.IsEnabled = false;
        }

        private void RadioButton_Checked(object sender, RoutedEventArgs e)
        {
            queryType = 1;
        }

        private void RadioButton_Checked_1(object sender, RoutedEventArgs e)
        {
            queryType = 2;
        }

        private void RadioButton_Checked_2(object sender, RoutedEventArgs e)
        {
            queryType = 3;
        }

        private void btnQuery_Click(object sender, RoutedEventArgs e)
        {
            string strSchName =(txtSchool.Text).Trim();
            string strClsName = (txtClass.Text).Trim();
            string strStuName = (txtStudent.Text).Trim();
            
            //三种查询模式，分别以学校、班级、学生为目标，如：以学校为目标是，
            //即使没有班、学生，也显示学校，相当于左连接或外连接。
            //但实际查询条件如果复杂些，就会提示错误，子表为空时主表查不出来
            //还需再研究
            using (var context = new AppDbContext()) {
                //组合查询条件
                switch (queryType)
                {
                    case 1:
                        {
                            /*
                            var query = from school in context.Schools
                                        join cls in context.Classes on school.SchoolId equals cls.SchoolId into sch_clses
                                        from sc in sch_clses.DefaultIfEmpty(new Class { Name = "" })
                                        join stu in context.Students on sc.ClassId equals stu.ClassId into sch_cls_stu
                                        from stu in sch_cls_stu.DefaultIfEmpty(new Student { StudentId = -1, Name = "", Gender = "", EnrollDate = (DateTime)default })
                            */
                            var query = from school in context.Schools
                                        join cls in context.Classes.DefaultIfEmpty() on school.SchoolId equals cls.SchoolId
                                        join stu in context.Students.DefaultIfEmpty() on cls.ClassId equals stu.ClassId
                                        select new
                                        {
                                            sname = school.Name,
                                            cname = cls.Name,
                                            id = stu.StudentId,
                                            name = stu.Name,
                                            gender = stu.Gender,
                                            edate = stu.EnrollDate
                                        };

                            if (strSchName != "")
                            {
                                query = query.Where(p => p.sname.Contains(strSchName));
                            }
                            if (strClsName != "")
                            {
                                query = query.Where(p => p.cname.Contains(strClsName));
                            }
                            if (strStuName != "")
                            {
                                query = query.Where(p => p.name.Contains(strStuName));
                            }
                            if (ckGender.IsChecked == true)
                            {
                                query = query.Where(p => p.gender == cbxGender.SelectedItem.ToString());
                            }
                            if (ckDate.IsChecked == true)
                            {
                                query = query.Where(p => ((p.edate > dtpFrom.SelectedDate) && (p.edate < dtpTo.SelectedDate)));
                            }
                            query.ToList();

                            lvwQueryResult.Items.Clear();
                            foreach (var s in query)
                            {
                                lvwQueryResult.Items.Add(s);
                            }

                        }
                        break;
                    case 2:
                        {
                            var query = from cls in context.Classes
                                        join sch in context.Schools on cls.SchoolId equals sch.SchoolId
                                        join stu in context.Students.DefaultIfEmpty() on cls.ClassId equals stu.ClassId
                                        select new
                                        {
                                            sname = sch.Name,
                                            cname = cls.Name,
                                            id = stu.StudentId,
                                            name = stu.Name,
                                            gender = stu.Gender,
                                            edate = stu.EnrollDate
                                        };
                            if (strSchName != "")
                            {
                                query = query.Where(p => p.sname.Contains(strSchName));
                            }
                            if (strClsName != "")
                            {
                                query = query.Where(p => p.cname.Contains(strClsName));
                            }
                            if (strStuName != "")
                            {
                                query = query.Where(p => p.name.Contains(strStuName));
                            }
                            if (ckGender.IsChecked == true)
                            {
                                query = query.Where(p => p.gender == cbxGender.SelectedItem.ToString());
                            }
                            if (ckDate.IsChecked == true)
                            {
                                query = query.Where(p => ((p.edate > dtpFrom.SelectedDate) && (p.edate < dtpTo.SelectedDate)));
                            }
                            query.ToList();
                            lvwQueryResult.Items.Clear();
                            foreach (var s in query)
                            {
                                lvwQueryResult.Items.Add(s);
                            }
                        }

                        break;
                    case 3:
                        {
                            var query = from stu in context.Students
                                        join cls in context.Classes on stu.ClassId equals cls.ClassId
                                        join sch in context.Schools on cls.SchoolId equals sch.SchoolId
                                        select new
                                        {
                                            sname = sch.Name,
                                            cname = cls.Name,
                                            id = stu.StudentId,
                                            name = stu.Name,
                                            gender = stu.Gender,
                                            edate = stu.EnrollDate
                                        };
                            if (strSchName != "")
                            {
                                query = query.Where(p => p.sname.Contains(strSchName));
                            }
                            if (strClsName != "")
                            {
                                query = query.Where(p => p.cname.Contains(strClsName));
                            }
                            if (strStuName != "")
                            {
                                query = query.Where(p => p.name.Contains(strStuName));
                            }
                            if (ckGender.IsChecked == true)
                            {
                                query = query.Where(p => p.gender == cbxGender.SelectedItem.ToString());
                            }
                            if (ckDate.IsChecked == true)
                            {
                                query = query.Where(p => ((p.edate > dtpFrom.SelectedDate) && (p.edate < dtpTo.SelectedDate)));
                            }

                            query.ToList();
                            lvwQueryResult.Items.Clear();
                            foreach (var s in query)
                            {
                                lvwQueryResult.Items.Add(s);
                            }
                        }
                        break;
                    default:
                        break;
                }
            }
        }

        private void ckGender_Checked(object sender, RoutedEventArgs e)
        {
            cbxGender.SelectedIndex = 0;
            cbxGender.IsEnabled = true;
        }

        private void ckDate_Checked(object sender, RoutedEventArgs e)
        {
            dtpFrom.IsEnabled = true;
            dtpTo.IsEnabled = true;

        }

        private void ckGender_Unchecked(object sender, RoutedEventArgs e)
        {
            cbxGender.SelectedIndex = -1;
            cbxGender.IsEnabled = false;
        }

        private void ckDate_Unchecked(object sender, RoutedEventArgs e)
        {
            dtpFrom.IsEnabled = false;
            dtpTo.IsEnabled = false;
        }
    }
}
